#!/usr/bin/env python
# -*- coding: utf-8 -*-

''' Autor: Bruno Alves '''

import sqlite3
import sys
import xlrd
import csv
import matplotlib.pyplot as plt
import numpy as np

'''Class to create the graphical statistics'''
class Graphics():
    def __init__(self):
        '''Class constructor'''
        connection = sqlite3.connect("Database.db")
        connection.text_factory = str  #to avoid erros when selecting non-utf data

        cursor = connection.cursor()

        cursor.execute('''select * from Data''')

        self.dataList = []


        for line in cursor.fetchall():
            self.dataList.append([line[0], line[1].replace('♦', '"'), line[2].replace('♦', '"'), line[3].replace('♦', '"'),
                             line[4].replace('♦', '"'), line[5].replace('♦', '"'), line[6], line[7], line[8], line[9],
                             line[10], line[11], line[12], line[13], line[14], line[15], line[16], line[17], line[18],
                             line[19], line[20], line[21]])

            pass


        self.course = set()
        self.education_level = set()

        for line in self.dataList:
            self.education_level.add(line[3]) 
            self.course.add(line[4])
            pass

        self.course = list(self.course)
        self.education_level = list(self.education_level)

        self.course.sort()
        self.education_level.sort()
        connection.close()
        pass
        

        
    def graphicStatistic1(self, course):
        '''Method to create the graphic for the first statistic'''
        '''@param course -> name of the course to search the total number of students'''
        '''@reference: http://matplotlib.org/examples/pylab_examples/barchart_demo.html'''
        
        year9596 = 0
        year9697 = 0
        year9798 = 0
        year9899 = 0
        year9900 = 0
        year0001 = 0
        year0102 = 0
        year0203 = 0
        year0304 = 0
        year0405 = 0
        year0506 = 0
        year0607 = 0
        year0708 = 0
        year0809 = 0
        year0910 = 0
        year1011 = 0
        
        
        listYears = []
        for line in self.dataList:
            if(line[4] == course):
                if(line[6] == '-'):
                    year9596 = year9596 + 0
                    pass
                else:
                    year9596 = year9596 + line[6]
          
                if(line[7] == '-'):
                    year9697 = year9697 + 0
                    pass
                else:
                    year9697 = year9697 + line[7]
  
                if(line[8] == '-'):
                    year9798 = year9798 + 0
                    pass
                else:
                    year9798 = year9798 + line[8]

                if(line[9] == '-'):
                    year9899 = year9899 + 0
                    pass
                else:
                    year9899 = year9899 + line[9]
                    pass

                if(line[10] == '-'):
                    year9900 = year9900 + 0
                    pass
                else:
                    year9900 = year9900 + line[10]
                    pass

                if(line[11] == '-'):
                    year0001 = year0001 + 0
                    pass
                else:
                    year0001 = year0001 + line[11]
                    pass

                if(line[12] == '-'):
                    year0102 = year0102 + 0
                    pass
                else:
                    year0102 = year0102 + line[12]
                    pass

                if(line[13] == '-'):
                    year0203 = year0203 + 0
                    pass
                else:
                    year0203 = year0203 + line[13]
                    pass


                if(line[14] == '-'):
                    year0304 = year0304 + 0
                    pass
                else:
                    year0304 = year0304 + line[14]
                    pass

                if(line[15] == '-'):
                    year0405 = year0405 + 0
                    pass
                else:
                    year0405 = year0405 + line[15]
                    pass

                if(line[16] == '-'):
                    year0506 = year0506 + 0
                    pass
                else:
                    year0506 = year0506 + line[16]
                    pass

                if(line[17] == '-'):
                    year0607 = year0607 + 0
                    pass
                else:
                    year0607 = year0607 + line[17]
                    pass

                if(line[18] == '-'):
                    year0708 = year0708 + 0
                    pass
                else:
                    year0708 = year0708 + line[18]
                    pass

                if(line[19] == '-'):
                    year0809 = year0809 + 0
                    pass
                else:
                    year0809 = year0809 + line[19]
                    pass

                if(line[20] == '-'):
                    year0910 = year0910 + 0
                    pass
                else:
                    year0910 = year0910 + line[20]
                    pass

                if(line[21] == '-'):
                    year1011 = year1011 + 0
                    pass
                else:
                    year1011 = year1011 + line[21]
                    pass

                pass
            pass

        pass
        listYears.append(year9596)
        listYears.append(year9697)
        listYears.append(year9798)
        listYears.append(year9899)
        listYears.append(year9900)
        listYears.append(year0001)
        listYears.append(year0102)
        listYears.append(year0203)
        listYears.append(year0304)
        listYears.append(year0405)
        listYears.append(year0506)
        listYears.append(year0607)
        listYears.append(year0708)
        listYears.append(year0809)
        listYears.append(year0910)
        listYears.append(year1011)
        
        numberOfBars = len(listYears)
        ind = np.arange(numberOfBars)
        width = (0.5)
        plt.subplot(111)
        bars1 = plt.bar(ind + width * 3, listYears, width, color='g')

        plt.xlabel('Years')
        plt.ylabel('Number of Students')
        plt.title('Graphic 1 - ' + course.decode('utf-8'))
        plt.xticks(ind + width * 3.5, ('95', '96', '97', '98', '99', '00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10'))

        def autolabel(bars):
            '''Method to show values on top of each bar'''
            '''@param bars -> name of the plot'''
            for bar in bars:
                height = bar.get_height()
                plt.text(bar.get_x() + bar.get_width() / 2, 1 * height, '%d'%int(height), ha='center', va='bottom')
                pass
            pass

        autolabel(bars1)
        plt.savefig('Graphic1.png')
        plt.show()
        pass
  
    pass


    def graphicStatistic2(self, education_level):
        '''Method to create the graphic for the second statistic'''
        '''@param education_level -> education level to search the total number of students'''
        '''@reference: http://matplotlib.org/examples/pylab_examples/barchart_demo.html'''
       
        year9596 = 0
        year9697 = 0
        year9798 = 0
        year9899 = 0
        year9900 = 0
        year0001 = 0
        year0102 = 0
        year0203 = 0
        year0304 = 0
        year0405 = 0
        year0506 = 0
        year0607 = 0
        year0708 = 0
        year0809 = 0
        year0910 = 0
        year1011 = 0
        
        
        listYears = []
        for line in self.dataList:
            if(line[3] == education_level):
                if(line[6] == '-'):
                    year9596 = year9596 + 0
                    pass
                else:
                    year9596 = year9596 + line[6]
          
                if(line[7] == '-'):
                    year9697 = year9697 + 0
                    pass
                else:
                    year9697 = year9697 + line[7]
  
                if(line[8] == '-'):
                    year9798 = year9798 + 0
                    pass
                else:
                    year9798 = year9798 + line[8]

                if(line[9] == '-'):
                    year9899 = year9899 + 0
                    pass
                else:
                    year9899 = year9899 + line[9]
                    pass

                if(line[10] == '-'):
                    year9900 = year9900 + 0
                    pass
                else:
                    year9900 = year9900 + line[10]
                    pass

                if(line[11] == '-'):
                    year0001 = year0001 + 0
                    pass
                else:
                    year0001 = year0001 + line[11]
                    pass

                if(line[12] == '-'):
                    year0102 = year0102 + 0
                    pass
                else:
                    year0102 = year0102 + line[12]
                    pass

                if(line[13] == '-'):
                    year0203 = year0203 + 0
                    pass
                else:
                    year0203 = year0203 + line[13]
                    pass


                if(line[14] == '-'):
                    year0304 = year0304 + 0
                    pass
                else:
                    year0304 = year0304 + line[14]
                    pass

                if(line[15] == '-'):
                    year0405 = year0405 + 0
                    pass
                else:
                    year0405 = year0405 + line[15]
                    pass

                if(line[16] == '-'):
                    year0506 = year0506 + 0
                    pass
                else:
                    year0506 = year0506 + line[16]
                    pass

                if(line[17] == '-'):
                    year0607 = year0607 + 0
                    pass
                else:
                    year0607 = year0607 + line[17]
                    pass

                if(line[18] == '-'):
                    year0708 = year0708 + 0
                    pass
                else:
                    year0708 = year0708 + line[18]
                    pass

                if(line[19] == '-'):
                    year0809 = year0809 + 0
                    pass
                else:
                    year0809 = year0809 + line[19]
                    pass

                if(line[20] == '-'):
                    year0910 = year0910 + 0
                    pass
                else:
                    year0910 = year0910 + line[20]
                    pass

                if(line[21] == '-'):
                    year1011 = year1011 + 0
                    pass
                else:
                    year1011 = year1011 + line[21]
                    pass

                pass
            pass

        pass
        listYears.append(year9596)
        listYears.append(year9697)
        listYears.append(year9798)
        listYears.append(year9899)
        listYears.append(year9900)
        listYears.append(year0001)
        listYears.append(year0102)
        listYears.append(year0203)
        listYears.append(year0304)
        listYears.append(year0405)
        listYears.append(year0506)
        listYears.append(year0607)
        listYears.append(year0708)
        listYears.append(year0809)
        listYears.append(year0910)
        listYears.append(year1011)
        
        numberOfBars = len(listYears)
        ind = np.arange(numberOfBars)
        width = (0.5)
        plt.subplot(111)
        bars2 = plt.bar(ind + width * 3, listYears, width, color='b')

        plt.xlabel('Years')
        plt.ylabel('Number of Students')
        plt.title('Graphic 2 - ' + education_level.decode('utf-8'))
        plt.xticks(ind + width * 3.5, ('95', '96', '97', '98', '99', '00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10'))

        def autolabel(bars):
            '''Method to show the values on top of the bars'''
            '''@param bars -> name of the plot'''
            for bar in bars:
                height = bar.get_height()
                plt.text(bar.get_x() + bar.get_width() / 2, 1 * height, '%d'%int(height), ha='center', va='bottom')
                pass
            pass

        autolabel(bars2)
        plt.savefig('Graphic2.png')
        plt.show()
        pass
  
    pass

            
                

        

        
    
